import os
import pandas as pd
import numpy as np
import openpyxl
from openpyxl.styles import PatternFill, Font
import time


# default table info and service time.
tables_lst = ['001', '002', '003', '004', '005', '006', '007', '008', 'A1', 'A2', 'B1', 'B2', 'C1', 'C2']
t0 = [str(idx+1) for idx in range(8)]
# tables_lst = ['001', '002']
time_lst = []
time_lst1 = []
for t in range(10, 24):
    t1 = "{}:00:00-{}:30:00".format(t, t)
    t2 = "{}:30:00-{}:00:00".format(t, t + 1)
    time_lst.extend([t1, t2])
    time_lst1.extend([t1[:8], t2[:8]])
time_lst1.append("24:00:00")
fill_red = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')
fill_blue = PatternFill(start_color='00AAFF', end_color='00AAFF', fill_type='solid')

def check_pth(pth):
    if not os.path.exists(pth):
        print("Fake Path!!! Please check")
        print(pth)
        exit(1)
    if pth[-5:] != '.xlsx':
        print("Wrong File!!! Please check")
        print(pth)
        exit(1)


def build_output_template():
    data = {}
    default_lst = ['-' for _ in range(len(time_lst))]
    # tables['table'] = tables_lst
    for idx, table in enumerate(tables_lst):
        data[table] = default_lst[:]

    # data = {"001": [0, 0], "002": [0, 0]}
    # data = {"calories": [420, 380, 390],"duration": [50, 40, 45]}
    # time_lst = ["day1", "day2", "day3"]
    df1 = pd.DataFrame(data, index=time_lst)
    # print(data)
    # print(df1)
    # exit()
    clr_map = np.zeros_like(df1)
    for i in range(clr_map.shape[0]):
        for j in range(clr_map.shape[1]):
            clr_map[i, j] = None
    return df1, clr_map


# def paint(serial):
#     blue = f"color: black; background-color:blue"
#     red = f"color: black; background-color:red"
#     normal = f"color: black"
#     lst = []
#     for val in serial:
#         if val == '-' or val[0] != '冲':
#             color = blue
#         elif val[0] == '冲':
#             color = red
#         else:
#             color = normal
#         lst.append(color)
#
#     print(lst)
#     return lst


def add_customer(df1, idx, t, time1, time2, room0, custmer_info):
    if not (time1 <= t < time2):
        return 0
    if df1.loc[time_lst[idx], room0] != '-':
        print("contradiction")
    if time1 == t:
        if df1.loc[time_lst[idx], room0] != '-':
            df1.loc[time_lst[idx], room0] = "冲 " + custmer_info
        else:
            df1.loc[time_lst[idx], room0] = custmer_info
    else:
        if df1.loc[time_lst[idx], room0] != '-' and df1.loc[time_lst[idx], room0] != '冲 订':
            df1.loc[time_lst[idx], room0] = '冲 订'
        else:
            df1.loc[time_lst[idx], room0] = '订'


def extract(pth1, pth2, sheet_in='in', sheet_out='out'):
    check_pth(pth1)

    # read xlsx
    df = pd.read_excel(pth1, sheet_name=sheet_in, index_col=False)
    df1, clr_map = build_output_template()
    # print(df)
    # print(df1)
    # exit()
    for idx, row in df.iterrows():
        time1 = str(row['到店时间'])
        time2 = str(row['离店时间'])
        if time2 == 'nan' or time2[:8] == '1900/1/1':
            time2 = '24:00:00'
        if len(time1) != 8 or len(time1) != 8:
            print('Invalid time!!!')
            print(row)
            exit(1)

        # print(time_lst1)
        # print(row)
        if time1 not in time_lst1:
            if "00" < time1[3:5] < "30":
                time1 = time1[:2] + ':00:00'
            else:
                time1 = time1[:2] + ':30:00'
        if time2 not in time_lst1:
            if "00" < time2[3:5] < "30":
                time2 = time2[:2] + ':30:00'
            else:
                temp = time2[:2] + ':30:00'
                time2 = time_lst1[time_lst1.index(temp)+1]
        # print(row['顾客姓名'], time1, time2)
        room0 = str(row['桌位号'])
        if room0 in t0:
            room0 = '00' + room0
        # room0 = room0.upper()
        if len(room0) < 2:
            print("Wrong room!!!")
            print(row)
            exit(1)
        if room0[0].isalpha():
            room0 = room0[0].upper() + room0[1:]
        if room0 not in tables_lst:
            print("Wrong room!!!")
            print(row)
            exit(1)
        custmer_info = "{} {} {}".format(idx+2, row['顾客姓名'], row['电话号码'])
        # print(time1, time2, custmer_info)
        # exit()
        for idx1, time in enumerate(time_lst):
            # print(df1.loc[time_lst[idx], '001'])
            t = time[:8]
            add_customer(df1, idx1, t, time1, time2, room0, custmer_info)
            # paint(clr_map, clr, (idx, tables_lst.index(room0)))
        # exit()
    # df1.style.apply(paint)
    # print(df1)
    # print('here')
    writer = pd.ExcelWriter(pth2)
    # book = load_workbook(pth)
    # writer.book = book
    df1.to_excel(writer, sheet_name=sheet_out)
    df.to_excel(writer, sheet_name=sheet_in, index=False)
    writer.save()

    wb = openpyxl.load_workbook(pth2)
    ws = wb['out']
    for ri, r in enumerate(ws):
        if ri == 0:
            continue
        for ci, c in enumerate(r):
            if ci == 0:
                continue
            # print(c, c.value)
            val = c.value
            if val is None or len(val) == 0:
                continue
            elif val[0] not in ['-', '冲']:
                c.fill = fill_blue
            elif val[0] == '冲':
                c.fill = fill_red
    wb.save(pth2)
    print('Success!')


if __name__ == '__main__':
    print(time.asctime())
    # pth1 = "/Users/steven/Desktop/lulutilu/table_order/no1_table_order.xlsx"
    print(os.getcwd())
    pth1 = os.getcwd() + "/no1_table_order_in.xlsx"
    pth2 = os.getcwd() + "/no1_table_order_out.xlsx"
    # pth1 = "/Users/steven/myrepo/git_space/lulutilu_bg/table_order/no1_table_order_in.xlsx"
    # pth2 = "/Users/steven/myrepo/git_space/lulutilu_bg/table_order/no1_table_order_out.xlsx"
    extract(pth1, pth2)
